clear all
cap close
set more off

cd "."

* ==============================================================================
* Aggregate McDash loan-level data (Proprietary owned by ICE) to county-quarter level
* ==============================================================================

foreach yr of numlist 2007/2017{
foreach m of numlist  1 4 7 10{

use lps`yr'm`m',clear

* drop missing zip
destring prop_zip,replace
drop if prop_zip==.

* drop jumbo
drop if jumbo_flg=="Y"
drop jumbo_flg

* drop FHA/VA (about 25%)
keep if inlist(loan_type,"C","D")  
drop loan_type

 * current quarter
gen month=m(`yr'm`m')
format month %tm
 
gen quarter=qofd(dofm(month))
format quarter %tq

* origination quarter
gen ori_qt=qofd(close_dt)
format ori_qt %tq
drop if ori_qt==quarter   // drop loans orginated in the current month

* termination quarter
gen term_qt=qofd(termination_dt)
format term_qt %tq
drop if term_qt<quarter  //N(t-1): All loans originated in the past and active at begining of this month
drop close_dt  termination_dt


* new refi and purchase in previous quarter
gen newpur_prev_q    =(ori_qt==quarter-1 & inlist(purpose_type_mcdash,"1"))
gen newrefi_prev_q   =(ori_qt==quarter-1 & inlist(purpose_type_mcdash,"2","3","5") )
gen newrefico_prev_q =(ori_qt==quarter-1 & inlist(purpose_type_mcdash,"2") )
gen newrefinc_prev_q =(ori_qt==quarter-1 & inlist(purpose_type_mcdash,"3") )
gen newrefiuk_prev_q =(ori_qt==quarter-1 & inlist(purpose_type_mcdash,"5") )
gen ex_prev_q = (ori_qt<quarter-1)

* national-level outstanding rates and new rates
egen nat_avgrate_prev = mean(cur_int_rate)

qui sum cur_int_rate if newpur_prev_q==1
gen nat_newpur_avgrate_prev = r(mean)

qui sum cur_int_rate if newrefi_prev_q==1
gen nat_newrefi_avgrate_prev = r(mean)

qui sum cur_int_rate if newrefico_prev_q==1
gen nat_newrefico_avgrate_prev = r(mean)

qui sum cur_int_rate if newrefinc_prev_q==1
gen nat_newrefinc_avgrate_prev = r(mean)

qui sum cur_int_rate if newrefiuk_prev_q==1
gen nat_newrefiuk_avgrate_prev = r(mean)

qui sum cur_int_rate if ex_prev_q==1
gen nat_ex_prev = r(mean)

* county level
rename prop_zip zip
merge m:1 zip using Econdata\zip_county_crosswalk
keep if _merge==3
drop _merge 

 * current-level var
sort county

  * counts
by county:  gen N_prev=_N   // # all active loans in prev quarter
by county: egen newpur_prev    = sum(newpur_prev_q)  // newpur in prev qtr
by county: egen newrefi_prev   = sum(newrefi_prev_q) // newrefi in prev qtr
by county: egen newrefico_prev = sum(newrefico_prev_q) // newrefi in prev qtr
by county: egen newrefinc_prev = sum(newrefinc_prev_q) // newrefi in prev qtr
by county: egen newrefiuk_prev = sum(newrefiuk_prev_q) // newrefi in prev qtr
by county: egen ex_prev = sum(ex_prev_q) // newrefi in prev qtr


  * avg rates
sort county  
by county: egen avgrate_prev= mean(cur_int_rate)   // all loans

replace newpur_prev_q=. if newpur_prev_q==0
sort county
by county: egen avgrate_newpur_prev=mean(newpur_prev_q*cur_int_rate)  // newpur

replace newrefi_prev_q=. if newrefi_prev_q==0
sort county
by county: egen avgrate_newrefi_prev=mean(newrefi_prev_q*cur_int_rate)  // newrefi

replace newrefico_prev_q=. if newrefico_prev_q==0
sort county
by county: egen avgrate_newrefico_prev=mean(newrefico_prev_q*cur_int_rate)  // newrefi

replace newrefinc_prev_q=. if newrefinc_prev_q==0
sort county
by county: egen avgrate_newrefinc_prev=mean(newrefinc_prev_q*cur_int_rate)  // newrefi

replace newrefiuk_prev_q=. if newrefiuk_prev_q==0
sort county
by county: egen avgrate_newrefiuk_prev=mean(newrefiuk_prev_q*cur_int_rate)  // newrefi

replace ex_prev_q=. if ex_prev_q==0
sort county
by county: egen avgrate_ex_prev=mean(ex_prev_q*cur_int_rate)  // newrefi

  * controls
replace fico=. if fico==0
replace ltv_mtm=. if ltv_mtm==0
sort county  
by county: egen avgfico_prev = mean(fico)
by county: egen avgltv_prev = mean(ltv_mtm)
by county: keep if _n==1

keep county quarter *_prev 

save mcdash`yr'm`m',replace
}
}


* combine quarterly data
clear
foreach yr of numlist 2007/2017{
foreach m of numlist 1 4 7 10{
append using mcdash`yr'm`m'
}
}

replace quarter=quarter-1
rename *_prev *

gen newpur_sr = newpur/N   // new pur in the pool of currently active loans
gen newrefi_sr= newrefi/N   // new refi in the pool of currently active loans

xtset county quarter

foreach x of varlist new*_sr{  // propensity in pps
replace `x'=`x'*100
}

keep quarter county newrefi_sr 
save mcdash4merge,replace
